from datetime import date, timedelta
from DataAccess.DBConnFactory import DBConnFactory
from Accounting.Routine.Settle import *
from Accounting.Routine.OpenDayOp import *

from Position.CalcDailyPosition import *
from Mark2Mkt.CalcMktValue import *


def create_client_CHOPP():
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
	#pke_conn = DBConnFactory().get_db_connection('PKEDB')
	#pke_cursor = pke_conn.cursor()	

	cursor.execute('''select distinct client_id 
					from official_share_chg
					where (transaction2='RED' or transaction2='SUB') 
					and (feeder_id2='SC028_US' or feeder_id2='SC028_NONUS') ''')
	for c in cursor.fetchall():
		client_id = c[0]
		if client_id=='HML035574' or client_id=='HML035561':
			open_day_create_client(client_id, True, True)	
		else:
			open_day_create_client(client_id, True, False)
			
			
def incepition_setup_CHOPP():
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
	
	inception = date(2007, 9, 10)
	v = [(inception.isoformat(),'SC028_NONUS',0,100,'USD','GAV','T'),
		(inception.isoformat(),'SC028_NONUS',0,100,'USD','NAV','T'),
		(inception.isoformat(),'SC028',0,100,'USD','GAV','T'), 
		(inception.isoformat(),'SC028',0,100,'USD','NAV','T'),
		(date(2007,9,30).isoformat(),'SC028_US',0,100,'USD','NAV','T'),
		(date(2007,9,30).isoformat(),'SC028_US',0,100,'USD','GAV','T'),
		(date(2007,9,9).isoformat(),'SC028_NONUS',0,100,'USD','GAV','T'),
		(date(2007,9,9).isoformat(),'SC028_NONUS',0,100,'USD','NAV','T'),
		(date(2007,9,9).isoformat(),'SC028',0,100,'USD','GAV','T'), 
		(date(2007,9,9).isoformat(),'SC028',0,100,'USD','NAV','T')]
		
	#insert into asset_value
	#(ref_date,entity,av,avpu,curncy,type,official)
	#values(TO_DATE('2007-09-30','yyyy-mm-dd'),'SC028_US',0,100,'USD','GAV','T');
	#insert into asset_value
	#(ref_date,entity,av,avpu,curncy,type,official)
	#values(TO_DATE('2007-09-30','yyyy-mm-dd'),'SC028_US',0,100,'USD','NAV','T');
	#commit;	
		
	sql_text = '''insert into asset_value
				(ref_date,entity,av,avpu,curncy,type,official)
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7)'''	
	cursor.executemany(sql_text, v)
	conn.commit()
		
	cursor.execute('''select client_id,feeder_id2,cash_amt
					from official_share_chg
					where ref_date=TO_DATE('2007-09-10','yyyy-mm-dd')
					  and feeder_id2 in ('SC028_NONUS','SC028_US')
					  and transaction2='SUB' ''')
	for item in cursor.fetchall():
		client = item[0]
		feeder = item[1]
		cash_amt = item[2]
		open_day_purchase(inception, client, cash_amt, 'USD', feeder, 'SC028')
		
	open_day_redemption_by_feeder(inception, 'SC028_NONUS', 84.50, 'USD')				
	
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	sql_text = '''insert into cash_switch
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)'''
	v = [(inception.isoformat(),'SC028',17019915.50,'USD')]
	pke_cursor.executemany(sql_text, v)
	pke_conn.commit()
	
	v = [(inception.isoformat(),'SC028','9999999','CASH',0,1,'GBP'),
		(inception.isoformat(),'SC028','9999999','CASH',17019915.50,1,'USD'),
		(inception.isoformat(),'SC028','9999999','CASH',0,1,'HKD')]
	sql_text = '''insert into position
				(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7)'''
	pke_cursor.executemany(sql_text, v)
	pke_conn.commit()	
	
	
	
def run_CHOPP(start_date):
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
		
	#start_date = date(2007, 9, 11)
	end_date = date(2012, 12, 31)
	one_day = timedelta(days=1)
	trade_date = start_date
	
	sql_tpl = Template('''select client_id,'SUB',feeder_id2,
						sum(abs(cash_amt)),sum(abs(share_amt)),'',ref_date,''
						from official_share_chg
						where transaction2='SUB'
						  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						  and feeder_id2 like 'SC028%'
						group by client_id,feeder_id2,ref_date
					UNION
						select client_id,'RED',feeder_id2,
						sum(abs(cash_amt)),sum(abs(share_amt)),
							red_ref_id,red_ref_date,detail	
						from official_share_chg
						where transaction2='RED'
						  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						  and feeder_id2 like 'SC028%'
						group by client_id,feeder_id2,red_ref_id,red_ref_date,detail
						''')
						  
	sql_tpl2 = Template('''select id from share_chg
						where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						  and type='subscription'
						  and child='${CLIENT}' 
						  ${DETAIL} ''')
	
	while trade_date<=end_date:
		print 'settlement date=', trade_date.isoformat(), '...'
		
		update_position_driven_by_dividend(trade_date)
		update_position_driven_by_transaction(trade_date)
		update_daily_position(trade_date)
		
		calc_portf_mkt_val(trade_date)
	
		daily_clearing(trade_date)

		#DEBUG
		start = timeit.default_timer()
		
		cursor.execute(sql_tpl.substitute(DATE=trade_date.isoformat()))
		r = cursor.fetchall()
		for act in r:
			client_id = act[0]
			tran = act[1]
			feeder_id = act[2]
			cash_amt = act[3]
			share_amt = act[4]
			
			if feeder_id=='SC028':
				if tran=='SUB':
					open_day_purchase_by_feeder(trade_date, client_id, cash_amt, 'USD')
				else:
					open_day_redemption_by_feeder(trade_date, client_id, cash_amt, 'USD')
				
			else:
				if tran=='SUB':
					open_day_purchase(trade_date, client_id, cash_amt, 'USD', feeder_id, master_id='SC028')
				else: #tran=='RED'
					red_ref_client = act[5]
					red_ref_date = act[6].date()
					detail = act[7]
					
					detail_critera = ''
					detail_critera2 = ''
					if detail and len(detail)>0:
						detail_critera = " and detail='" + detail +"' " 
						detail_critera2 = detail_critera
					else:
						detail_critera2 = " and nvl(detail,'other')<>'mgt_fee_rebate' " 
						
					cursor.execute(sql_tpl2.substitute(DATE=red_ref_date.isoformat(),
													CLIENT=red_ref_client,
													DETAIL=detail_critera))
					t = cursor.fetchall()
					if len(t)>1:
						cursor.execute(sql_tpl2.substitute(DATE=red_ref_date.isoformat(),
													CLIENT=red_ref_client,
													DETAIL=detail_critera2))
						t2 = cursor.fetchall()
						if len(t2)>1:												
							print "Find more than one sub records at ", red_ref_date.isoformat(), red_ref_client
							print t2
							return
						
						corr_sub_id = t2[0][0]
					elif (not t) or len(t)==0:
						print 'Can not find the corresponding sub record at ', red_ref_date.isoformat(), red_ref_client
						return
					else:
						corr_sub_id = t[0][0]
					
					if feeder_id=='SC028_NONUS':
						open_day_redemption(trade_date, client_id, None, 'USD', share_amt, False, 
								corr_sub_id)
					else: #'SC028_US'
						open_day_redemption(trade_date, client_id, cash_amt, 'USD', None, True, 
								corr_sub_id)
		
		elapsed = (timeit.default_timer() - start)
		print 'open day sub/red, ', elapsed		
		
			
		reflect_to_portf_cash_account(trade_date)
		calc_portf_mkt_val(trade_date, True)
		trade_date += one_day

		
		
def clear_all_record_after_CHOPP(ref_date):
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	
	str_date = ref_date.isoformat()	
	conn.cursor().execute("delete from asset_value where official='F' and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	conn.cursor().execute("delete from cash_chg where ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")	
	conn.cursor().execute("delete from fee where official='F' and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	conn.cursor().execute("delete from share_chg where ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	conn.commit()
	
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_conn.cursor().execute("delete from portfolio_mkt_val where ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.cursor().execute("delete from position where portfolio_id='SC028' and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.cursor().execute("delete from cash_switch where ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.cursor().execute("delete from cash_dividend_flow where event_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.cursor().execute("delete from stock_dividend_flow where event_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.commit()

	
	
def clear_before_inception():
	conn = DBConnFactory().get_db_connection('ACCOUNTING')	
	conn.cursor().execute('''delete from asset_value 
							where entity like 'SC028%' ''')
	conn.cursor().execute('''delete from cash_chg 
							where ref_date>=TO_DATE('2007-09-10','yyyy-mm-dd')
							''')	
	conn.cursor().execute('''delete from fee 
							where official='F' ''')
	conn.cursor().execute('''delete from share_chg 
							where ref_date>=TO_DATE('2007-09-10','yyyy-mm-dd')
							and parent like 'SC028%' ''')
	conn.commit()
	
	pke_conn = DBConnFactory().get_db_connection('PKEDB')	
	pke_conn.cursor().execute('''delete from position 
								where ref_date>=TO_DATE('2007-09-10','yyyy-mm-dd')
								  and portfolio_id='SC028' ''')
	pke_conn.cursor().execute('''delete from cash_switch
								where ref_date>=TO_DATE('2007-09-10','yyyy-mm-dd')
								  and portfolio_id='SC028' ''')
	pke_conn.cursor().execute('''truncate table portfolio_mkt_val''')
	pke_conn.cursor().execute('''truncate table cash_dividend_flow''')
	pke_conn.cursor().execute('''truncate table stock_dividend_flow''')
	pke_conn.commit()
	
	
def m2m_CHOPP():
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	pke_cursor.execute('''truncate table tmp_mkt_val''')
	pke_conn.commit

	pke_cursor.execute('''select distinct ref_date
						from official_mkt_val
						where ref_date<=TO_DATE('2012-11-30','yyyy-mm-dd')
						and ref_date>=TO_DATE('2007-09-30','yyyy-mm-dd')
						order by ref_date ''')
	
	sql_tpl = Template('''insert into tmp_mkt_val
					select TO_DATE('${DATE}','yyyy-mm-dd'),
						'SC028','SC028','NON_FUT', 
						sum(round(m2m.mkt_val/fx.price,2)),'USD'
					from mark_to_mkt m2m 
					join comm_fx_latest fx 
						on fx.ref_date=m2m.ref_date
						and fx.dom_curncy=m2m.local_curncy 
						and fx.for_curncy='USD'
					    and m2m.security_type<>'CASH'
					    and m2m.security_type<>'FUTURES'
					    and m2m.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					''')
	sql_tpl2 = Template('''insert into tmp_mkt_val
					select TO_DATE('${DATE}','yyyy-mm-dd'),
						'SC028','SC028','FUT', 
						sum(round(m2m.mkt_val/fx.price,2)),'USD'
					from mark_to_mkt m2m 
					join comm_fx_latest fx 
						on fx.ref_date=m2m.ref_date
						and fx.dom_curncy=m2m.local_curncy 
						and fx.for_curncy='USD'
					    and m2m.security_type='FUTURES'
					    and m2m.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					''')
					
	#print pke_cursor.fetchall()	
	#return
	
	for item in pke_cursor.fetchall():
		d = item[0]		
		pke_cursor.execute(sql_tpl.substitute(DATE=d.date().isoformat()))
		pke_cursor.execute(sql_tpl2.substitute(DATE=d.date().isoformat()))
		print d, ' completed...'
	
		pke_conn.commit()
		
		
	
def import_CH_OPPS_trade_records():
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	#pke_cursor.execute('''truncate table transaction''')
	#pke_conn.commit()
	
	trade_record_dir = "D:/work/pke/CS Cash Ledger/"
	
	start_date = date(2007, 9, 11)
	end_date = date(2012, 8, 31)
	one_day = timedelta(days=1)
	trade_date = start_date
	
	while trade_date<end_date:
		import_daily_trades(trade_date, trade_record_dir)
		trade_date += one_day
	
	return
	

def calc_CH_OPPS_position():
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	pke_cursor.execute('''delete from position 
						where ref_date>TO_DATE('2007-09-10','yyyy-mm-dd')
						  and portfolio_id='SC028' ''')
	pke_cursor.execute('''truncate table cash_dividend_flow''')
	pke_cursor.execute('''truncate table stock_dividend_flow''')
	pke_conn.commit()
	
	start_date = date(2007, 9, 11)
	#start_date = date(2011, 10, 22)
	end_date = date(2012, 8, 31)
	one_day = timedelta(days=1)
	trade_date = start_date
	
	'''
	insert into position
	(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
	values(TO_DATE('2007-09-10','yyyy-mm-dd'),'SC028','9999999','CASH',17020000.00,1.0,'USD');
	insert into position
	(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
	values(TO_DATE('2007-09-10','yyyy-mm-dd'),'SC028','9999999','CASH',0.00,1.0,'HKD');   
	commit;
	'''
	
	while trade_date<end_date:
		#start = timeit.default_timer()
		update_position_driven_by_dividend(trade_date)
		#elapsed = (timeit.default_timer() - start)
		#print 'dividend, ', elapsed
		
		start = timeit.default_timer()
		update_position_driven_by_transaction(trade_date)
		elapsed = (timeit.default_timer() - start)
		print 'transaction, ', elapsed
		
		print 'info, ', trade_date.isoformat(), ' position complete.'		
		trade_date += one_day
	
	return
	
		